package com.ridaizu.dao.impl;

import static com.google.common.base.Preconditions.checkNotNull;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.faces.bean.ApplicationScoped;
import javax.faces.bean.ManagedBean;

import com.ridaizu.dao.DaoAdapter;
import com.ridaizu.dao.db.DBAccess;
import com.ridaizu.model.Goods;
import com.ridaizu.model.Model;
import com.ridaizu.model.search.SearchModel;

@ManagedBean(name = "goodsDao")
@ApplicationScoped
public class GoodsDao extends DaoAdapter {

	private static final long serialVersionUID = 1L;

	public int selectCount(SearchModel searchModel) throws SQLException {

		StringBuffer sql = new StringBuffer();
		sql.append("select count(id) from m_goods \n");
		sql.append(searchModel.getSelectCountSql());

		Object[] params = searchModel.getConditionList().toArray();

		ResultSet rs = DBAccess.query(sql.toString(), params);

		if (rs == null) {
			return -1;
		}

		rs.next();

		return rs.getInt(1);
	}

	public Map<String, Goods> selectGoodsNameMap() throws SQLException {

		StringBuffer sql = new StringBuffer();
		sql.append("SELECT id, name FROM m_goods \n");

		ResultSet rs = DBAccess.query(sql.toString());

		Map<String, Goods> goodsMap = new HashMap<String, Goods> ();
		while (rs.next()) {
			Goods goods = new Goods();
			goods.setId(rs.getInt("id"));
			goods.setName(rs.getString("name"));

			goodsMap.put(goods.getName(), goods);
		}

		rs.close();

		return goodsMap;
	}

	public Map<Integer, Goods> selectGoodsIdMap() throws SQLException {

		StringBuffer sql = new StringBuffer();
		sql.append("SELECT id, name FROM m_goods \n");

		ResultSet rs = DBAccess.query(sql.toString());

		Map<Integer, Goods> goodsMap = new HashMap<Integer, Goods> ();
		while (rs.next()) {
			Goods goods = new Goods();
			goods.setId(rs.getInt("id"));
			goods.setName(rs.getString("name"));

			goodsMap.put(goods.getId(), goods);
		}

		rs.close();

		return goodsMap;
	}

	public List<Goods> selectGoodsList() throws SQLException {

		StringBuffer sql = new StringBuffer();
		sql.append("SELECT id, name FROM m_goods \n");

		ResultSet rs = DBAccess.query(sql.toString());

		List<Goods> modelList = new ArrayList<Goods> ();
		while (rs.next()) {
			Goods goods = new Goods();
			goods.setId(rs.getInt("id"));
			goods.setName(rs.getString("name"));

			modelList.add(goods);
		}

		rs.close();

		return modelList;
	}

	public List<Goods> selectGoodsList(SearchModel searchModel) throws SQLException {

		StringBuffer sql = new StringBuffer();
		sql.append("SELECT * FROM m_goods \n");
		sql.append(searchModel.getSelectSql());

		Object[] params = searchModel.getConditionList().toArray();

		ResultSet rs = DBAccess.query(sql.toString(), params);

		List<Goods> modelList = new ArrayList<Goods> ();
		while (rs.next()) {
			modelList.add((Goods)getModel(rs));
		}

		rs.close();

		return modelList;
	}

	@Override
	public Model getModel(ResultSet rs) throws SQLException {

		checkNotNull(rs);
		Goods goods = new Goods();
		goods.setId(rs.getInt("id"));
		goods.setName(rs.getString("name"));
		goods.setTaobaoName(rs.getString("taobao_name"));
		goods.setGoodsCode(rs.getString("goods_code"));
		goods.setBarcode(rs.getString("barcode"));
		goods.setGoodsTypeId(rs.getInt("goods_type_id"));
		goods.setCapacity(rs.getString("capacity"));
		goods.setWeight(rs.getInt("weight"));
		goods.setWholesalePrice(rs.getDouble("wholesale_price"));
		goods.setPrice(rs.getDouble("price"));
		goods.setSalesPrice(rs.getDouble("sales_price"));
		goods.setSummary(rs.getString("summary"));
		goods.setDetail(rs.getString("detail"));
		goods.setIngredient(rs.getString("ingredient"));
		goods.setEffect(rs.getString("effect"));
		goods.setSuitAgeId(rs.getInt("suit_age_id"));
		goods.setInstructions(rs.getString("instructions"));
		goods.setPrecautions(rs.getString("precautions"));
		goods.setGoodsUrl(rs.getString("goods_url"));
		goods.setPhotoUrl(rs.getString("photo_url"));
		goods.setThumbnailsUrl(rs.getString("thumbnails_url"));
		goods.setDelFlg(rs.getBoolean("del_flg"));
		goods.setRgtOpt(rs.getString("rgt_opt"));
		goods.setRgtDate(rs.getTimestamp("rgt_date"));
		goods.setUpdOpt(rs.getString("upd_opt"));
		goods.setUpdDate(rs.getTimestamp("upd_date"));

		return goods;
	}

	@Override
	public int insert(Model model) {

		checkNotNull(model);

		StringBuffer sql = new StringBuffer();
		sql.append("INSERT INTO m_goods \n");
		sql.append("( \n");
		sql.append("name, taobao_name, goods_code, barcode, goods_type_id, \n");
		sql.append("capacity, weight, wholesale_price, price, \n");
		sql.append("sales_price, summary, detail, ingredient, \n");
		sql.append("effect, suit_age_id, instructions, precautions, \n");
		sql.append("goods_url, photo_url, thumbnails_url, del_flg, \n");
		sql.append("rgt_opt, rgt_date, upd_opt, upd_date \n");
		sql.append(") \n");
		sql.append("VALUES \n");
		sql.append("(?, ?, ?, ?, ?, \n");
		sql.append("?, ?, ?, ?, \n");
		sql.append("?, ?, ?, ?, \n");
		sql.append("?, ?, ?, ?, \n");
		sql.append("?, ?, ?, ?, \n");
		sql.append("?, ?, ?, ?); \n");

		Goods goods = (Goods) model;

		Object[] params = new Object[] {
				goods.getName(), goods.getTaobaoName(), goods.getGoodsCode(), goods.getBarcode(), goods.getGoodsTypeId(),
				goods.getCapacity(), goods.getWeight(), goods.getWholesalePrice(), goods.getPrice(),
				goods.getSalesPrice(), goods.getSummary(), goods.getDetail(), goods.getIngredient(),
				goods.getEffect(), goods.getSuitAgeId(), goods.getInstructions(), goods.getPrecautions(),
				goods.getGoodsUrl(), goods.getPhotoUrl(), goods.getThumbnailsUrl(), goods.isDelFlg(),
				goods.getRgtOpt(), goods.getRgtDate(), goods.getUpdOpt(), goods.getUpdDate()
		};


		return DBAccess.update(sql.toString(), params);
	}

	@Override
	public int update(Model model) {

		checkNotNull(model);

		StringBuffer sql = new StringBuffer();
		sql.append("UPDATE m_goods \n");
		sql.append("SET \n");
		sql.append("name = ?, \n");
		sql.append("taobao_name = ?, \n");
		sql.append("goods_code = ?, \n");
		sql.append("barcode = ?, \n");
		sql.append("goods_type_id = ?, \n");
		sql.append("capacity = ?, \n");
		sql.append("weight = ?, \n");
		sql.append("wholesale_price = ?, \n");
		sql.append("price = ?, \n");
		sql.append("sales_price = ?, \n");
		sql.append("summary = ?, \n");
		sql.append("detail = ?, \n");
		sql.append("ingredient = ?, \n");
		sql.append("effect = ?, \n");
		sql.append("suit_age_id = ?, \n");
		sql.append("instructions = ?, \n");
		sql.append("precautions = ?, \n");
		sql.append("goods_url = ?, \n");
		sql.append("photo_url = ?, \n");
		sql.append("thumbnails_url = ?, \n");
		sql.append("del_flg = ?, \n");
		sql.append("rgt_opt = ?, \n");
		sql.append("rgt_date = ?, \n");
		sql.append("upd_opt = ?, \n");
		sql.append("upd_date = ? \n");
		sql.append("WHERE id = ? \n");

		Goods goods = (Goods) model;

		Object[] params = new Object[] {
				goods.getName(), goods.getTaobaoName(), goods.getGoodsCode(), goods.getBarcode(), goods.getGoodsTypeId(),
				goods.getCapacity(), goods.getWeight(), goods.getWholesalePrice(), goods.getPrice(),
				goods.getSalesPrice(), goods.getSummary(), goods.getDetail(), goods.getIngredient(),
				goods.getEffect(), goods.getSuitAgeId(), goods.getInstructions(), goods.getPrecautions(),
				goods.getGoodsUrl(), goods.getPhotoUrl(), goods.getThumbnailsUrl(), goods.isDelFlg(),
				goods.getRgtOpt(), goods.getRgtDate(), goods.getUpdOpt(), goods.getUpdDate(),
				goods.getId()
		};

		return DBAccess.update(sql.toString(), params);
	}

	public int delete(int id) {
		String sql = "DELETE FROM m_goods WHERE id=?;";

		return DBAccess.update(sql.toString(), id);
	}

}
